package com.hzncc.scaffold.mapper;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.toolkit.Constants;
import com.hzncc.scaffold.entity.Bo.InstituteRecordListBo;
import com.hzncc.scaffold.entity.Bo.RecordListBo;
import com.hzncc.scaffold.entity.Bo.TrainerListBo;
import com.hzncc.scaffold.entity.Other.Oneplusxrecord;
import com.hzncc.scaffold.entity.Vo.MajorDataVo;
import com.hzncc.scaffold.entity.Vo.ProvinceDataVo;
import com.hzncc.scaffold.entity.Vo.TitleDataVo;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Component;

import java.util.List;

/**
 * <p>
 * 1+X考试记录表 Mapper 接口
 * </p>
 *
 * @author DrunkBear
 * @since 2022-04-27
 */
@Component
public interface OneplusxrecordMapper extends BaseMapper<Oneplusxrecord> {


    public List<TrainerListBo> trainerList(@Param(Constants.WRAPPER) QueryWrapper queryWrapper,Integer type);

    @Select("SELECT oneplusxrecord.userId FROM `oneplusxrecord` WHERE oneplusxrecord.activityId IN (SELECT oneplusxtraining.id FROM oneplusxtraining ${ew.customSqlSegment})")
    public List<Integer> userIdList(@Param(Constants.WRAPPER) QueryWrapper queryWrapper);

    @Select("SELECT certificate.id FROM certificate WHERE certificate.recordId IN (SELECT oneplusxrecord.id FROM `oneplusxrecord` WHERE oneplusxrecord.activityId IN (SELECT oneplusxtraining.id FROM oneplusxtraining ${ew.customSqlSegment})) AND belongTo = 1")
    public List<Integer> certIdList(@Param(Constants.WRAPPER) QueryWrapper queryWrapper);

    @Select("SELECT SUBSTRING(`user`.idCardNum,7,4) FROM `user` WHERE user.id IN (SELECT oneplusxrecord.userId FROM `oneplusxrecord` WHERE oneplusxrecord.activityId IN (SELECT oneplusxtraining.id FROM oneplusxtraining ${ew.customSqlSegment}))")
    public List<Integer> yearList(@Param(Constants.WRAPPER) QueryWrapper queryWrapper);

    @Select("SELECT `user`.major AS name,COUNT(user.major) AS num FROM `user` WHERE user.id IN (SELECT oneplusxrecord.userId FROM `oneplusxrecord` WHERE oneplusxrecord.activityId IN (SELECT oneplusxtraining.id FROM oneplusxtraining ${ew.customSqlSegment})) GROUP BY user.major")
    public List<MajorDataVo> majorList(@Param(Constants.WRAPPER) QueryWrapper queryWrapper);

    @Select("SELECT\n" +
            "\tprovince.`name` AS province,\n" +
            "\tCOUNT(province.`name`) AS num\n" +
            "FROM\n" +
            "\t`user`\n" +
            "LEFT JOIN province ON province.id = SUBSTRING( `user`.idCardNum, 1, 2 ) \n" +
            "WHERE\n" +
            "\tuser.id IN (\n" +
            "\tSELECT\n" +
            "\t\toneplusxrecord.userId \n" +
            "\tFROM\n" +
            "\t\t`oneplusxrecord` \n" +
            "WHERE\n" +
            "\toneplusxrecord.activityId IN ( SELECT oneplusxtraining.id FROM oneplusxtraining ${ew.customSqlSegment})) GROUP BY province.`name`")
    public List<ProvinceDataVo> provinceList(@Param(Constants.WRAPPER) QueryWrapper queryWrapper);


    @Select("SELECT user.proTitle As name, COUNT(user.proTitle) as num FROM `user` WHERE user.id IN (SELECT oneplusxrecord.userId FROM `oneplusxrecord` WHERE oneplusxrecord.activityId IN (SELECT oneplusxtraining.id FROM oneplusxtraining ${ew.customSqlSegment})) GROUP BY `user`.proTitle")
    public List<TitleDataVo> titleList(@Param(Constants.WRAPPER) QueryWrapper queryWrapper);



//    @Select("SELECT oneplusxrecord.userId FROM `oneplusxrecord` WHERE oneplusxrecord.activityId IN (SELECT oneplusxtraining.id FROM oneplusxtraining ${ew.customSqlSegment})")
//    public List<Integer> test(@Param(Constants.WRAPPER) QueryWrapper queryWrapper);



    @Select("SELECT oneplusxrecord.userId FROM `oneplusxrecord` WHERE oneplusxrecord.activityId IN (SELECT oneplusxexam.id FROM oneplusxexam ${ew.customSqlSegment})")
    public List<Integer> userIdListTwo(@Param(Constants.WRAPPER) QueryWrapper queryWrapper);

    @Select("SELECT certificate.id FROM certificate WHERE certificate.recordId IN (SELECT oneplusxrecord.id FROM `oneplusxrecord` WHERE oneplusxrecord.activityId IN (SELECT oneplusxexam.id FROM oneplusxexam ${ew.customSqlSegment})) AND belongTo = 1")
    public List<Integer> certIdListTwo(@Param(Constants.WRAPPER) QueryWrapper queryWrapper);

    @Select("SELECT SUBSTRING(`user`.idCardNum,7,4) FROM `user` WHERE user.id IN (SELECT oneplusxrecord.userId FROM `oneplusxrecord` WHERE oneplusxrecord.activityId IN (SELECT oneplusxexam.id FROM oneplusxexam ${ew.customSqlSegment}))")
    public List<Integer> yearListTwo(@Param(Constants.WRAPPER) QueryWrapper queryWrapper);

    @Select("SELECT `user`.major AS name,COUNT(user.major) AS num FROM `user` WHERE user.id IN (SELECT oneplusxrecord.userId FROM `oneplusxrecord` WHERE oneplusxrecord.activityId IN (SELECT oneplusxexam.id FROM oneplusxexam ${ew.customSqlSegment})) GROUP BY user.major")
    public List<MajorDataVo> majorListTwo(@Param(Constants.WRAPPER) QueryWrapper queryWrapper);

    @Select("SELECT\n" +
            "\tprovince.`name` AS province,\n" +
            "\tCOUNT(province.`name`) AS num\n" +
            "FROM\n" +
            "\t`user`\n" +
            "LEFT JOIN province ON province.id = SUBSTRING( `user`.idCardNum, 1, 2 ) \n" +
            "WHERE\n" +
            "\tuser.id IN (\n" +
            "\tSELECT\n" +
            "\t\toneplusxrecord.userId \n" +
            "\tFROM\n" +
            "\t\t`oneplusxrecord` \n" +
            "WHERE\n" +
            "\toneplusxrecord.activityId IN ( SELECT oneplusxexam.id FROM oneplusxexam ${ew.customSqlSegment})) GROUP BY province.`name`")
    public List<ProvinceDataVo> provinceListTwo(@Param(Constants.WRAPPER) QueryWrapper queryWrapper);


    @Select("SELECT user.proTitle As name, COUNT(user.proTitle) as num FROM `user` WHERE user.id IN (SELECT oneplusxrecord.userId FROM `oneplusxrecord` WHERE oneplusxrecord.activityId IN (SELECT oneplusxexam.id FROM oneplusxexam ${ew.customSqlSegment})) GROUP BY `user`.proTitle")
    public List<TitleDataVo> titleListTwo(@Param(Constants.WRAPPER) QueryWrapper queryWrapper);



    @Select("SELECT\n" +
            "\toneplusxrecord.*,\n" +
            "\tuser.account,\n" +
            "\tuser.gender,\n" +
            "\tuser.photo,\n" +
            "\tuser.idCardNum,\n" +
            "\tuser.instituteName,\n" +
            "\tuser.`name` AS userName,\n" +
            "\tuser.realname,\n" +
            "\tuser.telephone,\n" +
            "\tcertificate.userId as userId,\n" +
            "\toneplusxtraining.activityEnd,\n" +
            "\toneplusxtraining.activityStart,\n" +
            "\toneplusxtraining.certTemplate,\n" +
            "\toneplusxtraining.coordinate,\n" +
            "\toneplusxtraining.id AS oneplusxtrainingId,\n" +
            "\toneplusxtraining.setHeight,\n" +
            "\toneplusxtraining.setWidth,\n" +
            "\toneplusxtraining.title,\n" +
            "\toneplusxtraining.certNumPrefix,\n" +
            "\tcertificate.id AS certificateId,\n" +
            "\tcertificate.certNum,\n" +
            "\tcertificate.imgUrl,\n" +
            "\tcertificate.belongTo\n" +
            "FROM\n" +
            "\t`oneplusxrecord`\n" +
            "\tLEFT JOIN certificate ON certificate.recordId = oneplusxrecord.id AND belongTo = 1 \n" +
            "\tLEFT JOIN `user` ON `user`.id = oneplusxrecord.userId\n" +
            "\tLEFT JOIN oneplusxtraining ON oneplusxtraining.id = oneplusxrecord.activityId    ${ew.customSqlSegment}")
    public List<RecordListBo> oneplusxrecordRecordList(@Param("activityId") String activityId,@Param(Constants.WRAPPER) QueryWrapper queryWrapper);


    @Select("SELECT\n" +
            "\toneplusxrecord.*,\n" +
            "\tuser.account,\n" +
            "\tuser.gender,\n" +
            "\tuser.photo,\n" +
            "\tuser.idCardNum,\n" +
            "\tuser.instituteName,\n" +
            "\tuser.`name` AS userName,\n" +
            "\tuser.realname,\n" +
            "\tuser.telephone,\n" +
            "\tcertificate.userId as certUserId,\n" +
            "\toneplusxexam.activityEnd,\n" +
            "\toneplusxexam.activityStart,\n" +
            "\toneplusxexam.`certTemplate`,\n" +
            "\toneplusxexam.coordinate,\n" +
            "\toneplusxexam.id AS oneplusxtrainingId,\n" +
            "\toneplusxexam.setHeight,\n" +
            "\toneplusxexam.setWidth,\n" +
            "\toneplusxexam.title,\n" +
            "\tcertificate.id AS certificateId,\n" +
            "\tcertificate.certNum,\n" +
            "\tcertificate.imgUrl,\n" +
            "\tcertificate.belongTo\n" +
            "FROM\n" +
            "\t`oneplusxrecord`\n" +
            "\tLEFT JOIN certificate ON certificate.recordId = oneplusxrecord.id AND belongTo = 1 \n" +
            "\tLEFT JOIN `user` ON `user`.id = oneplusxrecord.userId\n" +
            "\tLEFT JOIN oneplusxexam ON oneplusxexam.id = oneplusxrecord.activityId  ${ew.customSqlSegment}")
    public List<RecordListBo> oneplusxexamRecordList( @Param("activityId") String activityId, @Param(Constants.WRAPPER) QueryWrapper queryWrapper);







    @Select("SELECT\n" +
            "\t`oneplusxrecord`.`id`,\n" +
            "\t`oneplusxrecord`.`theoryScore`,\n" +
            "\t`oneplusxrecord`.`operationScore`,\n" +
            "\t`oneplusxrecord`.`passed`,\n" +
            "\t`oneplusxexam`.`id` AS `onePlusXExamId`,\n" +
            "\t`oneplusxexam`.`title` AS `onePlusXExamTitle`,\n" +
            "\t`oneplusxexam`.`examType` AS `onePlusXExamExamType`,\n" +
            "\t`oneplusxexam->profession`.`id` AS `onePlusXExamProfessionId`,\n" +
            "\t`oneplusxexam->profession`.`name` AS `onePlusXExamProfessionName`,\n" +
            "\t`oneplusxexam->skill`.`id` AS `onePlusXExamSkillId`,\n" +
            "\t`oneplusxexam->skill`.`name` AS `onePlusXExamSkillName`,\n" +
            "\t`owner`.`id` AS `ownerId`,\n" +
            "\t`owner`.`realname` AS `ownerRealname`,\n" +
            "\t`owner`.`telephone` AS `ownerTelephone`,\n" +
            "\t`owner`.`idCardNum` AS `ownerIdCardNum`,\n" +
            "\t`owner`.`instituteName` AS `ownerInstituteName`,\n" +
            "\t`owner`.`gender` AS `ownerGender`,\n" +
            "\t`certificate`.`id` AS `certificateId`,\n" +
            "\t`certificate`.`certNum` AS `certificateCertNum`,\n" +
            "\t`certificate`.`imgUrl` AS `certificateImgUrl` \n" +
            "FROM\n" +
            "\t`oneplusxrecord` AS `oneplusxrecord`\n" +
            "\tINNER JOIN `oneplusxexam` AS `oneplusxexam` ON `oneplusxrecord`.`activityId` = `oneplusxexam`.`id`\n " +
            "\tLEFT OUTER JOIN `profession` AS `oneplusxexam->profession` ON `oneplusxexam`.`professionId` = `oneplusxexam->profession`.`id`\n" +
            "\tLEFT OUTER JOIN `skill` AS `oneplusxexam->skill` ON `oneplusxexam`.`skillId` = `oneplusxexam->skill`.`id`\n" +
            "\tLEFT OUTER JOIN `user` AS `owner` ON `oneplusxrecord`.`userId` = `owner`.`id`\n" +
            "\tLEFT OUTER JOIN `certificate` AS `certificate` ON `oneplusxrecord`.`id` = `certificate`.`recordId` \n" +
            "\tAND `certificate`.`belongTo` = 1 \n ${ew.customSqlSegment}" +
            "ORDER BY\n" +
            "\t`oneplusxrecord`.`id` DESC ")
    public List<InstituteRecordListBo> InstituteRecordList(@Param(Constants.WRAPPER) QueryWrapper queryWrapper);
}
